﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SqlMeth
{
    public class Hallucinator
    {
        string _connection;
        string[] _avoid;
        ConfigSettings _settings;

        /// <summary>
        /// Initializes object
        /// </summary>
        /// <param name="connection">String path to database</param>
        public Hallucinator(string connection)
        {
            Me(connection, null);
        }

        /// <summary>
        /// Initializes object
        /// </summary>
        /// <param name="connection">String path to database</param>
        /// <param name="settings">Program configuration settings</param>
        public Hallucinator(string connection, ConfigSettings settings)
        {
            Me(connection, settings);
        }

        public void Me(string connection, ConfigSettings settings)
        {
            _connection = connection;

            if (settings != null)
            {
                _avoid = settings.Avoid.Split(',');
                _settings = settings;
            }
        }

        /// <summary>
        /// Lists all the tables in a database
        /// </summary>
        /// <param name="includeColumns">Indicates if columns should be obtained too.</param>
        /// <returns>List of tables in the connected database</returns>
        public List<DbTable> ListTables(bool includeColumns = false)
        {
            List<DbTable> tables = new List<DbTable>();

            using (SqlConnection myConnection = new SqlConnection(_connection))
            {
                using (SqlCommand myCommand = new SqlCommand("SELECT * FROM information_schema.tables WHERE TABLE_TYPE=\'BASE TABLE\'", myConnection))
                {
                    SqlDataReader reader = null;

                    if (myConnection.State != System.Data.ConnectionState.Open)
                        myConnection.Open();

                    reader = myCommand.ExecuteReader();

                    while (reader.Read())
                    {
                        string tName = reader["TABLE_NAME"].ToString();

                        if (_avoid != null)
                        {
                            foreach (string str in _avoid)
                            {
                                if (!tName.StartsWith(str))
                                {
                                    tables.Add(DbTable.Create(tName,
                                        reader["TABLE_CATALOG"].ToString(), reader["TABLE_SCHEMA"].ToString(),
                                        includeColumns ? ListTableColumns(tName) : null));
                                }
                            }
                        }
                        else
                        {
                            // Nothing to avoid
                            tables.Add(DbTable.Create(tName,
                                        reader["TABLE_CATALOG"].ToString(), reader["TABLE_SCHEMA"].ToString(),
                                        includeColumns ? ListTableColumns(tName) : null));
                        }
                    }
                }
            }

            if (includeColumns)
            {
                for (int i = 0; i < tables.Count; i++)
                {
                    AddTableConstraints(tables[i]);
                }
            }

            return tables;
        }

        /// <summary>
        /// Gets the columns for a table
        /// </summary>
        /// <param name="tableName">Table for which to obtain columns</param>
        /// <returns>The columns of the given table</returns>
        public List<DbColumn> ListTableColumns(string tableName)
        {
            List<DbColumn> columns = new List<DbColumn>();

            using (SqlConnection myConnection = new SqlConnection(_connection))
            {
                using (SqlCommand myCommand = new SqlCommand(String.Format("SELECT * " +
                        "FROM INFORMATION_SCHEMA.COLUMNS " +
                        "WHERE TABLE_NAME = N'{0}' ORDER BY ORDINAL_POSITION ASC", tableName),
                    myConnection))
                {
                    SqlDataReader reader = null;

                    if (myConnection.State != System.Data.ConnectionState.Open)
                        myConnection.Open();

                    reader = myCommand.ExecuteReader();

                    while (reader.Read())
                    {
                        string catalog = reader["TABLE_CATALOG"].ToString();
                        string schema = reader["TABLE_SCHEMA"].ToString();
                        string table = reader["TABLE_NAME"].ToString();
                        string colName = reader["COLUMN_NAME"].ToString();
                        string charMaxLength = reader["CHARACTER_MAXIMUM_LENGTH"].ToString();

                        columns.Add(DbColumn.Create(colName,
                            int.Parse(reader["ORDINAL_POSITION"].ToString()),
                            reader["DATA_TYPE"].ToString(),
                            String.IsNullOrEmpty(charMaxLength) ? 0 : int.Parse(charMaxLength),
                            reader["IS_NULLABLE"].ToString()));
                    }
                }
            }

            return columns;
        }

        /// <summary>
        /// Determines if a column for a table in a catalog and schema is a primary key
        /// </summary>
        /// <param name="catalog">Catalog of the table</param>
        /// <param name="schema">Schema of the table</param>
        /// <param name="table">Table name</param>
        /// <param name="column">Column name</param>
        /// <returns>True if the column is a primary key for given table</returns>
        public bool IsPrimaryKey(string catalog, string schema, string table, string column)
        {
            return IsKey(catalog, schema, table, column, "PRIMARY KEY");
        }

        /// <summary>
        /// Determines if a column for a table in a catalog and schema is a foreign key
        /// </summary>
        /// <param name="catalog">Catalog of the table</param>
        /// <param name="schema">Schema of the table</param>
        /// <param name="table">Table name</param>
        /// <param name="column">Column name</param>
        /// <returns>True if the column is a foreign key in the table</returns>
        public bool IsForeignKey(string catalog, string schema, string table, string column)
        {
            return IsKey(catalog, schema, table, column, "FOREIGN KEY");
        }

        /// <summary>
        /// Determines if a column for a table in a catalog and schema is of type "keyType" (foreign or primary)
        /// </summary>
        /// <param name="catalog">Catalog of the table</param>
        /// <param name="schema">Schema of the table</param>
        /// <param name="table">Table name</param>
        /// <param name="column">Column name</param>
        /// <param name="keyType">Type of key. Values are: "PRIMARY KEY" or "FOREIGN KEY"</param>
        /// <returns>True if the column is of "keyType"</returns>
        bool IsKey(string catalog, string schema, string table, string column, string keyType)
        {
            int count = 0;

            using (SqlConnection myConnection = new SqlConnection(_connection))
            {
                using (SqlCommand myCommand = new SqlCommand(String.Format("SELECT COUNT(*) " +
                        "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc " +
                        "JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name " +
                        "WHERE tc.CONSTRAINT_TYPE = \'{0}\' AND " +
                        "tc.TABLE_CATALOG = \'{1}\' AND " +
                        "tc.TABLE_SCHEMA = \'{2}\' AND " +
                        "tc.TABLE_NAME = \'{3}\' AND " +
                        "ccu.COLUMN_NAME = \'{4}\'",
                        keyType,
                        catalog,
                        schema,
                        table,
                        column),
                        myConnection))
                {
                    if (myConnection.State != System.Data.ConnectionState.Open)
                        myConnection.Open();

                    count = (int)myCommand.ExecuteScalar();
                }
            }

            return count > 0 ? true : false;
        }

        /// <summary>
        /// Finds and adds the foreign and primary key constraints of a table
        /// </summary>
        /// <param name="table">Table to add foreign and primary key constraints</param>
        public void AddTableConstraints(DbTable table)
        {
            List<DbConstraint> constraints = null;

            using (SqlConnection myConnection = new SqlConnection(_connection))
            {
                using (SqlCommand myCommand = new SqlCommand(String.Format("SELECT * " +
                        "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc " +
                        "JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name " +
                        "WHERE tc.TABLE_CATALOG = \'{0}\' AND " +
                        "tc.TABLE_SCHEMA = \'{1}\' AND " +
                        "tc.TABLE_NAME = \'{2}\' " +
                        "ORDER BY tc.CONSTRAINT_NAME",
                        table.Catalog,
                        table.Schema,
                        table.Name),
                        myConnection))
                {
                    // For each CONSTRAINT_NAME, create a new DbConstraint and add the columns
                    using (DataSet ds = new DataSet())
                    {
                        SqlDataAdapter da = new SqlDataAdapter(myCommand);

                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        da.Fill(ds);

                        // Keep track of the constraint's name, if it changes, it's another constraint
                        string oldConstraintName = null;
                        DbConstraint tableConstraint = null;
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            string newConstraintName = ds.Tables[0].Rows[i].Field<string>("CONSTRAINT_NAME");

                            if (newConstraintName != oldConstraintName)
                            {
                                if (constraints == null)
                                    constraints = new List<DbConstraint>();

                                // Add previous constraint
                                if(tableConstraint != null)
                                    constraints.Add(tableConstraint);
                                // Release old
                                tableConstraint = null;
                                // Create new
                                tableConstraint = DbConstraint.Create(ds.Tables[0].Rows[i].Field<string>("CONSTRAINT_NAME"),
                                    ds.Tables[0].Rows[i].Field<string>("CONSTRAINT_TYPE") == "PRIMARY KEY" ? true : false);
                            }
                            // Add column to constraint. This column has no info
                            tableConstraint.Columns.Add(DbColumn.Create(ds.Tables[0].Rows[i].Field<string>("COLUMN_NAME"), -1, null, 0, null));
                            oldConstraintName = newConstraintName;
                        }

                        if (constraints != null && tableConstraint != null)
                            constraints.Add(tableConstraint);
                    }
                }
            }

            // Need to make a pass through the table to complete the columns (instead of fetching from DB)
            foreach (DbConstraint dbc in constraints)
            {
                foreach (DbColumn c in dbc.Columns)
                {
                    DbColumn resultColumn = table.Columns.Single(t => t.Name == c.Name);
                    c.OrdinalPosition = resultColumn.OrdinalPosition;
                    c.DataType = resultColumn.DataType;
                    c.CharMaxLength = resultColumn.CharMaxLength;
                    c.AllowNulls = resultColumn.AllowNulls;
                }
            }

            table.Constraints = constraints;
        }

        /// <summary>
        /// Adds control columns to the table
        /// </summary>
        public void AddControlColumns()
        {
            Dictionary<string, string> columns = new Dictionary<string, string>(){ 
                                                     {"_CreatedBy", "NVARCHAR(64) NULL" }, 
                                                     {"_CreatedOn", "DATETIME NULL"}, 
                                                     {"_Disabled", "BIT NULL"}, 
                                                     {"_LastUpdatedBy", "NVARCHAR(64) NULL"}, 
                                                     {"_LastUpdatedOn", "DATETIME NULL"}, 
                                                     {"_VerCol", "ROWVERSION NULL"}
                                                 };

            // IMPORTANT: Once these columns are added, they need to be manually or removed by some other means than this
            // functionality.

            foreach (DbTable table in ListTables())
            {
                using (SqlConnection myConnection = new SqlConnection(_connection))
                {
                    // For each column that might be missing, add it
                    foreach (KeyValuePair<string, string> col in columns)
                    {
                        using (SqlCommand myCommand = new SqlCommand(string.Format("IF NOT EXISTS (SELECT * " +
                            "FROM INFORMATION_SCHEMA.COLUMNS " +
                            "WHERE TABLE_NAME=\'{0}\' AND COLUMN_NAME=\'{1}\') " +
                            "ALTER TABLE {0} ADD {1} {2}",
                            table.Name,
                            col.Key,
                            col.Value),
                            myConnection))
                        {
                            if (myConnection.State != System.Data.ConnectionState.Open)
                                myConnection.Open();

                            myCommand.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Eases the generation of the parameters section of a stored procedure by translating
        /// a list of columns to a single list of strings.
        /// </summary>
        /// <param name="columns">Columns to modify.</param>
        /// <returns>List of parameters, one per column.</returns>
        List<string> ConvertToListOfParams(List<DbColumn> columns)
        {
            List<string> result = new List<string>();

            foreach (DbColumn c in columns)
            {
                if (c.CharMaxLength != 0)
                    result.Add(string.Format("@{0} {1}({2})", c.Name, c.DataType, c.CharMaxLength == -1 ? "MAX" : c.CharMaxLength.ToString()));
                else
                    result.Add(string.Format("@{0} {1}", c.Name, c.DataType));
            }

            return result;
        }

        /// <summary>
        /// Generates an INSERT stored procedure.
        /// </summary>
        /// <param name="procName">Name of the stored procedure.</param>
        /// <param name="table">Table in which records written.</param>
        /// <returns>A stored procedure string.</returns>
        string GenerateCreateSprocScript(String procName, DbTable table)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendLine(String.Format("CREATE PROCEDURE {0}.{1} (", table.Schema, procName));

            // Append columns for stored procedure parameters
            var cols = from DbColumn c in table.Columns
                       where c.Name != "_VerCol" &&
                       c.Name != "_Disabled"
                       select c;

            builder.AppendLine(string.Join(",\n", ConvertToListOfParams(cols.ToList())));

            builder.AppendLine(")");
            builder.AppendLine("AS");
            builder.AppendLine("BEGIN");

            builder.AppendLine(Branding());

            builder.AppendLine(String.Format("INSERT INTO {0}.{1} ({2}, _Disabled) VALUES (@{3}, \'0\')", table.Schema, table.Name,
                String.Join(", ", cols), String.Join(", @", cols)));

            builder.AppendLine("END");

            return builder.ToString();
        }

        /// <summary>
        /// Generates a SELECT * stored procedure with "Skip" and "Take" parameters.
        /// </summary>
        /// <param name="procName">Name of the stored procedure.</param>
        /// <param name="table">Table from which records will be read.</param>
        /// <returns>A stored procedure string for SQL Server 2012 skip and take.</returns>
        private string GenerateSkipTakeSprocScript(string procName, DbTable table)
        {
            return GenerateReadAllScript("PROCEDURE", procName, table);
        }

        /// <summary>
        /// Generates a SELECT * stored procedure.
        /// </summary>
        /// <param name="procName">Name of the stored procedure.</param>
        /// <param name="table">Table from which records will be read.</param>
        /// <returns>A stored procedure string.</returns>
        string GenerateReadAllSprocScript(string procName, DbTable table)
        {
            return GenerateReadAllScript("PROCEDURE", procName, table);
        }

        /// <summary>
        /// Generates a view for a table
        /// </summary>
        /// <param name="viewName">Name of the view</param>
        /// <param name="table">Table for which the view is being created</param>
        /// <returns>A T-SQL CREATE command</returns>
        string GenerateViewScript(string viewName, DbTable table)
        {
            return GenerateReadAllScript("VIEW", viewName, table);
        }

        /// <summary>
        /// Generates either a VIEW or a PROCEDURE using SELECT *
        /// </summary>
        /// <param name="type">Enter either "VIEW" or "PROCEDURE"</param>
        /// <param name="procName">View or stored procedure name</param>
        /// <param name="table">Table for which this is being created</param>
        /// <returns>A T-SQL CREATE command</returns>
        string GenerateReadAllScript(string type, string procName, DbTable table)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendLine(String.Format("CREATE {0} {1}.{2}", type, table.Schema, procName));

            builder.AppendLine("AS");

            if (type.ToUpper() != "VIEW")
                builder.AppendLine("BEGIN");

            builder.AppendLine(Branding());

            builder.AppendLine(String.Format("SELECT * FROM {0}.{1}", table.Schema, table.Name));

            if (type.ToUpper() != "VIEW")
                builder.AppendLine("END");

            return builder.ToString();
        }

        /// <summary>
        /// Generates SELECT stored procedures using a whereCol.
        /// </summary>
        /// <param name="procName">Name of the stored procedure.</param>
        /// <param name="table">Table from which records will be read.</param>
        /// <param name="whereCols">Key or keys used to identify a record or records.</param>
        /// <returns>A stored procedure string.</returns>
        string GenerateSpecificReadSprocs(string procName, DbTable table, List<DbColumn> whereCols)
        {
            // Future improvement: Read using composite keys
            StringBuilder builder = new StringBuilder();
            builder.AppendLine(String.Format("CREATE PROCEDURE {0}.{1} (", table.Schema, procName));


            builder.AppendLine(string.Join(",\n", ConvertToListOfParams(whereCols)));

            builder.AppendLine(")");
            builder.AppendLine("AS");
            builder.AppendLine("BEGIN");

            builder.AppendLine(Branding());

            builder.Append(String.Format("SELECT * FROM {0}.{1} WHERE ", table.Schema, table.Name));
            for (int i = 0; i < whereCols.Count; i++)
            {
                builder.Append(string.Format("{0}=@{0}", whereCols[i].Name));

                if (i != (whereCols.Count - 1))
                {
                    builder.Append(" AND ");
                }
            }
            builder.AppendLine("");

            builder.AppendLine("END");

            return builder.ToString();
        }

        /// <summary>
        /// Eases the generation of the SET section of an UPDATE command by translating
        /// a list of columns to a single list of strings.
        /// </summary>
        /// <param name="columns">Columns to modify.</param>
        /// <returns>List of SET strings, one per column.</returns>
        List<string> ConvertToListOfSets(List<DbColumn> columns)
        {
            List<string> result = new List<string>();

            foreach (DbColumn c in columns)
            {
                result.Add(string.Format("{0}=@{0}", c.Name));
            }

            return result;
        }

        /// <summary>
        /// Generates UPDATE stored procedures using a whereCol and _VerCol.
        /// </summary>
        /// <param name="procName">Name of the stored procedure.</param>
        /// <param name="table">Table from which records will be updated.</param>
        /// <param name="whereCols">Keys used to identify a record or records to update.</param>
        /// <returns>A stored procedure string.</returns>
        string GenerateSpecificUpdateSprocs(string procName, DbTable table, List<DbColumn> whereCols)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendLine(String.Format("CREATE PROCEDURE {0}.{1} (", table.Schema, procName));

            // Append columns for stored procedure parameters
            List<DbColumn> parameters = (from DbColumn c in table.Columns
                                         where !table.Constraints.Single(cns => cns.IsPrimary).Columns.Contains(c) &&
                                         c.Name != "_CreatedBy" &&
                                         c.Name != "_CreatedOn"
                                         select c).ToList();
            
            foreach (DbColumn dc in whereCols)
            {
                if (!parameters.Contains(dc))
                    parameters.Add(dc);
            }

            builder.AppendLine(string.Join(",\n", ConvertToListOfParams(parameters.ToList())));

            builder.AppendLine(")");
            builder.AppendLine("AS");
            builder.AppendLine("BEGIN");

            builder.AppendLine(Branding());

            // Append columns for stored procedure set. Avoid allowing primary key updating
            var setSection = from DbColumn c in table.Columns
                             where !table.Constraints.Single(cts => cts.IsPrimary).Columns.Any(n => n.Name == c.Name) &&
                             c.Name != "_CreatedBy" &&
                             c.Name != "_CreatedOn" &&
                             c.Name != "_VerCol"
                             select c;

            builder.Append(String.Format("UPDATE {0}.{1} SET {2} WHERE ", table.Schema, table.Name,
                String.Join(",\n", ConvertToListOfSets(setSection.ToList()))));

            for (int i = 0; i < whereCols.Count; i++)
            {
                builder.Append(string.Format("{0}=@{0}", whereCols[i].Name));

                if (i != (whereCols.Count - 1))
                {
                    builder.Append(" AND ");
                }
            }
            builder.AppendLine(" AND _VerCol=@_VerCol");

            builder.AppendLine("END");

            return builder.ToString();
        }

        /// <summary>
        /// Generates DELETE stored procedures using a whereCol and _VerCol.
        /// </summary>
        /// <param name="procName">Name of the stored procedure.</param>
        /// <param name="table">Table from which records will be deleted.</param>
        /// <param name="whereCols">Collection of keys used to delete a record or records.</param>
        /// <returns>A stored procedure string.</returns>
        string GenerateSpecificDeleteSprocs(string procName, DbTable table, List<DbColumn> whereCols)
        {
            // Future improvement: Delete using composite keys
            StringBuilder builder = new StringBuilder();
            builder.AppendLine(String.Format("CREATE PROCEDURE {0}.{1} (", table.Schema, procName));

            builder.AppendLine(string.Join(",\n", ConvertToListOfParams(whereCols)));
            builder.AppendLine(",");
            builder.AppendLine("@_VerCol timestamp");
            builder.AppendLine(")");
            builder.AppendLine("AS");
            builder.AppendLine("BEGIN");

            builder.AppendLine(Branding());

            builder.Append(String.Format("DELETE FROM {0}.{1} WHERE ", table.Schema, table.Name));

            for (int i = 0; i < whereCols.Count; i++)
            {
                builder.Append(string.Format("{0}=@{0}", whereCols[i].Name));

                if (i != (whereCols.Count - 1))
                {
                    builder.Append(" AND ");
                }
            }

            builder.AppendLine(" AND _VerCol=@_VerCol");

            builder.AppendLine("END");

            return builder.ToString();
        }

        string Branding()
        {
            return String.Format("-- Created with \"SqlMeth\" on {0}. \"SqlMeth\" was developed by Elemental Enterprise Solutions Corp. Visit http://www.be-elemental.com.",
                DateTime.Now.ToString());
        }

        /// <summary>
        /// Drops old stored procedures and creates new CRUD procedures. These procedures also
        /// include procedures by primary key and foreign key (on read, update and delete).
        /// </summary>
        /// <returns>List of tables with their respective insert, update and delete procedures by PK.</returns>
        public List<DbTable> GenerateStoredProcedures()
        {
            string sprocNameMask = _settings.SprocNameMask; // Examples: "T0000-ReadAll", "T0000-Create"
            string actionMask = _settings.ActionMask; // Examples: "UpdateByID", "UpdateByForeignId" 
            string sProcName = null;

            // Load tables
            List<DbTable> tables = ListTables(true);

            // Create stored procedures. Drop older procedures
            foreach (DbTable table in tables)
            {
                using (SqlConnection myConnection = new SqlConnection(_connection))
                {
                    // Starts - Stored procedure for INSERT

                    // Drop old insert
                    sProcName = string.Format(sprocNameMask, table.Name, "Create");
                    using (SqlCommand myCommand = new SqlCommand(string.Format("IF OBJECT_ID(N\'{0}\', N\'P\') IS NOT NULL " +
                            "DROP PROCEDURE {0}", sProcName), myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        myCommand.ExecuteNonQuery();
                    }

                    // Create new insert
                    using (SqlCommand myCommand = new SqlCommand(GenerateCreateSprocScript(sProcName, table),
                        myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        myCommand.ExecuteNonQuery();
                        table.Insert = new DbSProc()
                        {
                            Name = sProcName,
                            Schema = table.Schema
                        };
                    }

                    // Ends - Stored procedure for INSERT

                    // Starts - Stored procedure for SELECT ALL

                    // Drop old select all
                    sProcName = string.Format(sprocNameMask, table.Name, "ReadAll");
                    using (SqlCommand myCommand = new SqlCommand(string.Format("IF OBJECT_ID(N\'{0}\', N\'P\') IS NOT NULL " +
                            "DROP PROCEDURE {0}", sProcName), myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        myCommand.ExecuteNonQuery();
                    }

                    // Create new select all
                    using (SqlCommand myCommand = new SqlCommand(GenerateReadAllSprocScript(sProcName, table),
                        myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        myCommand.ExecuteNonQuery();
                        table.ReadProcedures.Add(new DbSProc()
                        {
                            Name = sProcName,
                            Schema = table.Schema
                        });
                    }

                    // Ends - Stored procedure for SELECT ALL

                    // Starts - Stored procedures for Primary keys
                    
                    // Drop old select
                    sProcName = string.Format(sprocNameMask, table.Name,
                        string.Format(actionMask, "Read", table.Constraints.Single(c => c.IsPrimary).Columns.Count > 1 ? "PKs" : "PK"));
                    using (SqlCommand myCommand = new SqlCommand(string.Format("IF OBJECT_ID(N\'{0}\', N\'P\') IS NOT NULL " +
                            "DROP PROCEDURE {0}", sProcName),
                            myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        myCommand.ExecuteNonQuery();
                    }

                    // Create new select
                    using (SqlCommand myCommand = new SqlCommand(GenerateSpecificReadSprocs(sProcName, table, table.Constraints.Single(c => c.IsPrimary).Columns),
                        myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        myCommand.ExecuteNonQuery();
                        table.ReadProcedures.Add(new DbSProc()
                        {
                            Name = sProcName,
                            Schema = table.Schema
                        });
                    }

                    // Drop old update
                    sProcName = string.Format(sprocNameMask, table.Name,
                        string.Format(actionMask, "Update", table.Constraints.Single(c => c.IsPrimary).Columns.Count > 1 ? "PKs" : "PK"));
                    using (SqlCommand myCommand = new SqlCommand(string.Format("IF OBJECT_ID(N\'{0}\', N\'P\') IS NOT NULL " +
                            "DROP PROCEDURE {0}", sProcName),
                            myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        myCommand.ExecuteNonQuery();
                    }

                    // Create new update
                    using (SqlCommand myCommand = new SqlCommand(GenerateSpecificUpdateSprocs(sProcName, table, table.Constraints.Single(c => c.IsPrimary).Columns),
                        myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        myCommand.ExecuteNonQuery();

                        table.Update = new DbSProc()
                        {
                            Name = sProcName,
                            Schema = table.Schema
                        };
                    }

                    // Drop old delete
                    sProcName = string.Format(sprocNameMask, table.Name,
                        string.Format(actionMask, "Delete", table.Constraints.Single(c => c.IsPrimary).Columns.Count > 1 ? "PKs" : "PK"));
                    using (SqlCommand myCommand = new SqlCommand(string.Format("IF OBJECT_ID(N\'{0}\', N\'P\') IS NOT NULL " +
                            "DROP PROCEDURE {0}", sProcName),
                            myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        myCommand.ExecuteNonQuery();
                    }

                    // Create new delete
                    using (SqlCommand myCommand = new SqlCommand(GenerateSpecificDeleteSprocs(sProcName, table, table.Constraints.Single(c => c.IsPrimary).Columns),
                        myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        myCommand.ExecuteNonQuery();

                        table.Delete = new DbSProc()
                        {
                            Name = sProcName,
                            Schema = table.Schema
                        };
                    }

                    // Stored procedures for foreign constraints
                    foreach (DbConstraint fc in table.Constraints.Where(c => !c.IsPrimary))
                    {
                        // Drop old select
                        sProcName = string.Format(sprocNameMask, table.Name, string.Format(actionMask, "Read", fc.Name));
                        using (SqlCommand myCommand = new SqlCommand(string.Format("IF OBJECT_ID(N\'{0}\', N\'P\') IS NOT NULL " +
                                "DROP PROCEDURE {0}", sProcName),
                                myConnection))
                        {
                            if (myConnection.State != System.Data.ConnectionState.Open)
                                myConnection.Open();

                            myCommand.ExecuteNonQuery();
                        }

                        // Create new select
                        using (SqlCommand myCommand = new SqlCommand(GenerateSpecificReadSprocs(sProcName, table, fc.Columns),
                            myConnection))
                        {
                            if (myConnection.State != System.Data.ConnectionState.Open)
                                myConnection.Open();

                            myCommand.ExecuteNonQuery();
                            table.ReadProcedures.Add(new DbSProc()
                            {
                                Name = sProcName,
                                Schema = table.Schema
                            });
                        }

                        // Drop old update
                        sProcName = string.Format(sprocNameMask, table.Name, string.Format(actionMask, "Update", fc.Name));
                        using (SqlCommand myCommand = new SqlCommand(string.Format("IF OBJECT_ID(N\'{0}\', N\'P\') IS NOT NULL " +
                                "DROP PROCEDURE {0}", sProcName),
                                myConnection))
                        {
                            if (myConnection.State != System.Data.ConnectionState.Open)
                                myConnection.Open();

                            myCommand.ExecuteNonQuery();
                        }

                        // Create new update
                        using (SqlCommand myCommand = new SqlCommand(GenerateSpecificUpdateSprocs(sProcName, table, fc.Columns),
                            myConnection))
                        {
                            if (myConnection.State != System.Data.ConnectionState.Open)
                                myConnection.Open();

                            myCommand.ExecuteNonQuery();
                        }

                        // Drop old delete
                        sProcName = string.Format(sprocNameMask, table.Name, string.Format(actionMask, "Delete", fc.Name));
                        using (SqlCommand myCommand = new SqlCommand(string.Format("IF OBJECT_ID(N\'{0}\', N\'P\') IS NOT NULL " +
                                "DROP PROCEDURE {0}", sProcName),
                                myConnection))
                        {
                            if (myConnection.State != System.Data.ConnectionState.Open)
                                myConnection.Open();

                            myCommand.ExecuteNonQuery();
                        }

                        // Create new delete
                        using (SqlCommand myCommand = new SqlCommand(GenerateSpecificDeleteSprocs(sProcName, table, fc.Columns),
                            myConnection))
                        {
                            if (myConnection.State != System.Data.ConnectionState.Open)
                                myConnection.Open();

                            myCommand.ExecuteNonQuery();
                        }
                    }
                }
            }

            return tables;
        }

        /// <summary>
        /// Generates one SELECT * view per table. Drops any old views.
        /// </summary>
        /// <param name="tables">Tables to generate views.</param>
        /// <returns>Number of views generated.</returns>
        public int GenerateViews(List<DbTable> tables)
        {
            int counter = 0;

            using (SqlConnection myConnection = new SqlConnection(_connection))
            {
                foreach (DbTable table in tables)
                {
                    // Drop old view
                    using (SqlCommand myCommand = new SqlCommand(string.Format("IF OBJECT_ID(N\'{0}\', N\'V\') IS NOT NULL " +
                                                "DROP VIEW {0}", _settings.ViewPrefix + table.Name),
                                                myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        myCommand.ExecuteNonQuery();
                    }

                    // Create view
                    using (SqlCommand myCommand = new SqlCommand(GenerateViewScript(_settings.ViewPrefix + table.Name, table),
                        myConnection))
                    {
                        if (myConnection.State != System.Data.ConnectionState.Open)
                            myConnection.Open();

                        counter += myCommand.ExecuteNonQuery();
                    }
                }
            }

            return (counter * -1);
        }
    }

    public class DbTable
    {
        public string Name { get; set; }
        public string Catalog { get; set; }
        public string Schema { get; set; }
        public List<DbColumn> Columns { get; set; }
        public DbSProc Insert { get; set; }
        public DbSProc Update { get; set; }
        public DbSProc Delete { get; set; }
        public List<DbSProc> ReadProcedures { get; set; }
        public List<DbConstraint> Constraints { get; set; }

        public static DbTable Create(string name, string catalog, string schema, List<DbColumn> columns)
        {
            DbTable table = new DbTable();

            table.Catalog = catalog;
            table.Schema = schema;
            table.Name = name;
            table.Columns = columns;
            table.ReadProcedures = new List<DbSProc>();

            return table;
        }

        public override bool Equals(object obj)
        {
            if (obj == null) return false;
            DbTable objAsDbTable = obj as DbTable;
            if (objAsDbTable == null) return false;
            else return Equals(objAsDbTable);
        }

        public bool Equals(DbTable other)
        {
            if (other == null) return false;
            return (this.Name.Equals(other.Name) &&
                this.Catalog.Equals(other.Catalog) &&
                this.Schema.Equals(other.Schema) &&
                this.Columns.SequenceEqual(other.Columns));
        }

        public override string ToString()
        {
            return Name;
        }
    }

    public class DbConstraint
    {
        public string Name { get; set; }
        public bool IsPrimary { get; set; }
        public List<DbColumn> Columns { get; set; }

        public static DbConstraint Create(string name, bool isPrimary)
        {
            DbConstraint constraint = new DbConstraint();
            constraint.Name = name;
            constraint.IsPrimary = isPrimary;
            constraint.Columns = new List<DbColumn>();

            return constraint;
        }

        public override bool Equals(object obj)
        {
            if (obj == null) return false;
            DbConstraint objAsDbConstraint = obj as DbConstraint;
            if (objAsDbConstraint == null) return false;
            else return Equals(objAsDbConstraint);
        }

        public bool Equals(DbConstraint other)
        {
            if (other == null) return false;
            return (this.Name.Equals(other.Name) && this.IsPrimary.Equals(other.IsPrimary) && this.Columns.SequenceEqual(other.Columns));
        }

        public override string ToString()
        {
            return Name;
        }
    }

    public class DbColumn
    {
        public string Name { get; set; }
        //public bool IsPrimaryKey { get; set; }
        //public bool IsForeignKey { get; set; }
        public int OrdinalPosition { get; set; }
        public string DataType { get; set; }
        public int CharMaxLength { get; set; }
        public bool AllowNulls { get; set; }

        public static DbColumn Create(string name, int ordinal_position, string data_type, int charMaxLength, string is_nullable)
        {
            DbColumn column = new DbColumn();

            column.Name = name;
            //column.IsPrimaryKey = isPk;
            //column.IsForeignKey = isFk;
            column.OrdinalPosition = ordinal_position;
            column.DataType = data_type;
            column.CharMaxLength = charMaxLength;

            if (!String.IsNullOrEmpty(is_nullable))
                column.AllowNulls = is_nullable.ToUpper() == "YES" ? true : false;
            else
                column.AllowNulls = false;

            return column;
        }

        public override bool Equals(object obj)
        {
            if (obj == null) return false;
            DbColumn objAsDbColumn = obj as DbColumn;
            if (objAsDbColumn == null) return false;
            else return Equals(objAsDbColumn);
        }

        public bool Equals(DbColumn other)
        {
            if (other == null) return false;
            return (this.Name.Equals(other.Name) &&
                this.OrdinalPosition.Equals(other.OrdinalPosition) &&
                this.DataType.Equals(other.DataType) &&
                this.CharMaxLength.Equals(other.CharMaxLength));
        }

        public override string ToString()
        {
            return Name;
        }
    }

    public class DbSProc
    {
        public string Name { get; set; }
        public string Schema { get; set; }

        public override bool Equals(object obj)
        {
            if (obj == null) return false;
            DbSProc objAsDbSProc = obj as DbSProc;
            if (objAsDbSProc == null) return false;
            else return Equals(objAsDbSProc);
        }

        public bool Equals(DbSProc other)
        {
            if (other == null) return false;
            return (this.Name.Equals(other.Name) &&
                this.Schema.Equals(other.Schema));
        }

        public override string ToString()
        {
            return string.Format("{0}.{1}", Schema, Name);
        }
    }
}
